﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_report_dayoff')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_report_dayoff

GO

-- =============================================
-- Author:      YFC
-- Create date: 2020-07-10 23:57:45
-- Database:    scm_main_hysj20200702
-- Description: 报表管理-调休假额度报表
-- =============================================

CREATE PROCEDURE bd_proc_report_dayoff
  @year INT,
  @month  INT,
  @userNameOrNo VARCHAR(100) = '',  --工号或姓名
  @users  VARCHAR(MAX) = '',
  @depts  VARCHAR(MAX) = '',
  @user_type  VARCHAR(50) = '',
  @user_company VARCHAR(200) = '',
  @user_status  VARCHAR(20) = ''
AS
BEGIN
  SET NOCOUNT ON;
--工号、姓名、员工类型、所属公司、工作状态
  CREATE TABLE #result
  (
    user_serial BIGINT
    , user_no VARCHAR(20)
    , user_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS
    , dept_id BIGINT
    , dept_name VARCHAR(500) COLLATE Chinese_PRC_CI_AS
    , card_no VARCHAR(50) COLLATE Chinese_PRC_CI_AS --员工卡号
    , card_manhour VARCHAR(50) COLLATE Chinese_PRC_CI_AS  --员工属性工时制
    , company VARCHAR(200) COLLATE Chinese_PRC_CI_AS    --员工属性所属公司
    , user_type VARCHAR(50) COLLATE Chinese_PRC_CI_AS   --员工属性员工类型
    , user_status VARCHAR(50) COLLATE Chinese_PRC_CI_AS  --员工属性工作状态
    , dayoff_total INT DEFAULT 0     --查询时段内调休总额度
    , dayoff_use INT DEFAULT 0        --查询时段内使用的调休时数
    , dayoff_remain  INT DEFAULT 0  --查询时段内剩余的调休时数
  )

  DECLARE @sql VARCHAR(MAX) = ''

  SELECT @sql = '
    INSERT INTO #result (user_serial, user_no, user_name, dept_id, dept_name, card_no, card_manhour, company, user_type, user_status)
    SELECT user_serial, user_no, user_lname, user_dep, dep_name, card_number, man_hour_system, user_company, user_sny_lx, user_sny_type
    FROM bd_view_user_all u
    WHERE 1 = 1
  '
  IF(ISNULL(@userNameOrNo, '') <> '' AND ISNULL(@users, '') = '')
  BEGIN
  	SELECT @sql += ' AND u.user_no LIKE ''%' + @userNameOrNo + '%'' OR u.user_lname LIKE ''%' + @userNameOrNo + '%'''
  END

  IF(ISNULL(@users, '') <> '')
  BEGIN
  	SELECT @sql += ' AND u.user_serial IN(' + @users + ')'
  END

  IF(ISNULL(@depts, '') <> '')
  BEGIN
  	SELECT @sql += ' AND u.user_dep IN(' + @depts + ')'
  END

  IF(ISNULL(@user_type, '') <> '')
  BEGIN
  	SELECT @sql += ' AND user_sny_lx = ''' + @user_type + ''''
  END

  IF(ISNULL(@user_company, '') <> '')
  BEGIN
  	SELECT @sql += ' AND user_company LIKE ''%' + @user_company + '%'''
  END

  IF(ISNULL(@user_status, '') <> '')
  BEGIN
  	SELECT @sql += ' AND user_sny_type = ''' + @user_status + ''''
  END


  EXEC(@sql)


  DECLARE @query_year INT, @query_month INT

  SET @query_month = @month - 1
  IF(@query_month = 0)
  BEGIN
    --1月份减1个月为去掉12月份
  	SELECT @query_year = @year - 1, @query_month = 12
  END ELSE
  BEGIN
  	SET @query_year = @year
  END

  --调休总额度，取时间银行上月结算双休加班+平时加班
  --调休剩余额度，取时间银行调休额度
  UPDATE r
  SET dayoff_total = ISNULL(ht.save_ordinary, 0) + ISNULL(ht.save_weekend, 0), dayoff_remain = ISNULL(ht.rest_total, 0)
  FROM #result r
  INNER JOIN bd_kq_holidays_time ht ON ht.user_serial = r.user_serial AND ht.time_year = @query_year AND ht.time_month = @query_month

  --已使用额度
  UPDATE #result SET dayoff_use = dayoff_total - dayoff_remain

  SELECT * FROM #result order by user_no

  DROP TABLE #result

  SET NOCOUNT OFF;
END
GO